package edu.yuhf.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.yuhf.db.utils.JdbcTemplate;

import edu.yuhf.dao.iface.UsersDao;
import edu.yuhf.domain.UserDetail;
import edu.yuhf.domain.UserForm;
import edu.yuhf.domain.Users;

public class UsersJdbcDaoImpl implements UsersDao {

	@Override
	public int queryUserByNameAndPwd(String name, String password) {
		int rowNumber=0;
		String sql="select count(*) from users where name=? and password=?";
		rowNumber=JdbcTemplate.queryForCount(sql, new Object[] {name,password});
		return rowNumber;
	}

	@Override
	public int insertUser(Users user) {
		String sql="insert into users values(users_id.nextval,?,?,?,?)";
		int userId=0;
		userId=JdbcTemplate.updateForKey(sql, new String[] {"id"}, user.getName(),user.getPassword(),user.getSex(),user.getEmail());
		return userId;
	}

	@Override
	public int insertUserDetail(UserDetail ud) {
		int rowNumber=0;
		String sql="insert into userDetail values(userDetail_id.nextval,?,?,?)";
		rowNumber=JdbcTemplate.update(sql, new Object[] {ud.getNativePlaceCode(),ud.getHobbyCode(),ud.getUserId()});
		return rowNumber;
	}

	@Override
	public List<UserForm> queryUserAll() {
		String sql="select u.id,u.name,u.password,u.sex,u.email,ud.hobby_code,np.name from users u left outer join Userdetail ud on u.id=ud.userid left outer join nativeplace np on ud.nativeplace_code=np.code";
		List<UserForm> list=JdbcTemplate.query(sql, (rs)->{
			List<UserForm> list0=new ArrayList<>();
			try {
				while(rs.next()) {
					UserForm uf=new UserForm(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7));
					list0.add(uf);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return list0;
		}, new Object[] {});
		return list;
	}

	@Override
	public List<UserForm> queryUserByPage(int currentPage, int rowNumber) {
		StringBuffer sb=new StringBuffer("select * from (select bt.*,rownum rn from (");
		sb.append("select u.id id,u.name name,u.password password,u.sex sex,u.email email,ud.hobby_code hobbyCode,np.name nativePlaceName");
		sb.append(" from users u left outer join Userdetail ud on u.id=ud.userid left outer join nativeplace np on ud.nativeplace_code=np.code");
		sb.append(") bt where rownum<=(?)*?) mt where rn>(?-1)*?");
		List<UserForm> list=JdbcTemplate.query(sb.toString(), (rs)->{
			List<UserForm> list0=new ArrayList<>();
			try {
				while(rs.next()) {
					UserForm uf=new UserForm(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7));
					list0.add(uf);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return list0;
		}, new Object[] {currentPage,rowNumber,currentPage,rowNumber});
		return list;
	}

	@Override
	public int getTotalRow() {
		int rowNum=0;
		String sql="select count(1) from users u left outer join Userdetail ud on u.id=ud.userid left outer join nativeplace np on ud.nativeplace_code=np.code";
		rowNum=JdbcTemplate.queryForCount(sql, new Object[] {});
		return rowNum;
	}

}
